<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>mysql常用sql | iworkh blog</title>
  
    <meta name="keywords" content=" mysql ">
  
  <meta name="description" content="mysql常用sql | iworkh blog">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta name="description" content="个人简介沐雨云楼，程序员一名。 喜欢研究技术，主要从事JAVA、微服务、大数据领域。 联系方式 QQ : 157162006 邮箱 :157162006@qq.com">
<meta property="og:type" content="website">
<meta property="og:title" content="关于">
<meta property="og:url" content="https://iworkh.gitee.io/blog/about/index.html">
<meta property="og:site_name" content="iworkh blog">
<meta property="og:description" content="个人简介沐雨云楼，程序员一名。 喜欢研究技术，主要从事JAVA、微服务、大数据领域。 联系方式 QQ : 157162006 邮箱 :157162006@qq.com">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2020-05-17T07:22:16.000Z">
<meta property="article:modified_time" content="2020-09-12T13:21:47.523Z">
<meta property="article:author" content="沐雨云楼">
<meta property="article:tag" content="iworkh">
<meta property="article:tag" content="沐雨云楼">
<meta property="article:tag" content="blog">
<meta name="twitter:card" content="summary">


<link rel="icon" href="/blog/img/iwork.svg">

<link href="/blog/css/style.css?v=1.0.1" rel="stylesheet">

<link href="/blog/css/hl_theme/atom-light.css?v=1.0.1" rel="stylesheet">

<link href="//cdn.bootcdn.net/ajax/libs/animate.css/4.1.0/animate.min.css" rel="stylesheet">
<link href="//cdn.bootcdn.net/ajax/libs/font-awesome/5.13.0/js/fontawesome.min.js" rel="stylesheet">

<script src="//cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="/blog/js/jquery.autocomplete.min.js?v=1.0.1" ></script>
<script src="/blog/js/titleTip.js?v=1.0.1" ></script>

<script src="//cdn.bootcdn.net/ajax/libs/highlight.js/10.0.3/highlight.min.js"></script>
<script>
    hljs.initHighlightingOnLoad();
</script>

<script src="//cdn.bootcdn.net/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>



<script src="//cdn.bootcdn.net/ajax/libs/jquery-cookie/1.4.1/jquery.cookie.min.js" ></script>

<script src="/blog/js/iconfont.js?v=1.0.1" ></script>

<meta name="generator" content="Hexo 4.2.1"></head>
<div style="display: none">
  <input class="theme_disqus_on" value="false">
  <input class="theme_preload_comment" value="false">
  <input class="theme_blog_path" value="/blog">
</div>




<body>
<aside class="nav">
    <div class="nav-left">
        <a href="/blog/" class="avatar_target">
    <img class="avatar" src="/blog/img/iwork.svg" />
</a>
<div class="author">
    <span>沐雨云楼</span>
</div>

<div class="icon">
    
        
        <a title="github" href="https://gitee.com/iworkh" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-github"></use>
                </svg>
            
        </a>
        
    
        
        <a title="csdn" href="https://blog.csdn.net/u011622109" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-csdn"></use>
                </svg>
            
        </a>
        
    
        
        <a title="email" href="mailto:157162006@qq.com" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-email"></use>
                </svg>
            
        </a>
        
    
        
        <a title="qq" href="http://wpa.qq.com/msgrd?v=3&uin=157162006&site=qq&menu=yes" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-qq"></use>
                </svg>
            
        </a>
        
    
        
        <a title="kugou" href="https://www.kugou.com/" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-kugou"></use>
                </svg>
            
        </a>
        
    
        
        <a title="neteasemusic" href="https://music.163.com/" target="_blank">
            
                <svg class="iconfont-svg" aria-hidden="true">
                    <use xlink:href="#icon-neteasemusic"></use>
                </svg>
            
        </a>
        
    
</div>



<a class="more-menus">更多菜单</a>


<ul>
    <li><div class="all active">全部文章<small>(85)</small></div></li>
    
        
            
            <li><div data-rel="手册"><i class="fold iconfont icon-right"></i>手册<small>(2)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="iworkh">iworkh<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="pgmanor">pgmanor<small>(1)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="工具"><i class="fold iconfont icon-right"></i>工具<small>(16)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="git">git<small>(2)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="github">github<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="hexo">hexo<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="idea">idea<small>(2)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="markdowm">markdowm<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="maven">maven<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="有道云">有道云<small>(1)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="java"><i class="fold iconfont icon-right"></i>java<small>(20)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="dao">dao<small>(6)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="jvm">jvm<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="test">test<small>(5)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="tools">tools<small>(3)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="并发">并发<small>(1)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="微服务"><i class="fold iconfont icon-right"></i>微服务<small>(6)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="spring">spring<small>(2)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="springboot">springboot<small>(3)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="架构">架构<small>(2)</small></div>
                
            </li>
            
        
    
        
            
            <li><div data-rel="大数据"><i class="fold iconfont icon-right"></i>大数据<small>(4)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="hadoop">hadoop<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="spark">spark<small>(3)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="web"><i class="fold iconfont icon-right"></i>web<small>(3)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="vue">vue<small>(2)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="db"><i class="fold iconfont icon-right"></i>db<small>(4)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="mysql">mysql<small>(3)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="redis">redis<small>(1)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="ai">ai<small>(4)</small></div>
                
            </li>
            
        
    
        
            
            <li><div data-rel="python"><i class="fold iconfont icon-right"></i>python<small>(8)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="tornado">tornado<small>(3)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="linux"><i class="fold iconfont icon-right"></i>linux<small>(2)</small></div>
                
                    <ul class="sub hide">
                        
                        <li><div data-rel="docker">docker<small>(1)</small></div>
                            
                        </li>
                            
                        <li><div data-rel="nginx">nginx<small>(1)</small></div>
                            
                        </li>
                            
                    </ul>
                
            </li>
            
        
    
        
            
            <li><div data-rel="windows">windows<small>(3)</small></div>
                
            </li>
            
        
    
        
            
            <li><div data-rel="结构和算法">结构和算法<small>(8)</small></div>
                
            </li>
            
        
    
        
            
            <li><div data-rel="面试">面试<small>(2)</small></div>
                
            </li>
            
        
    
        
            
            <li><div data-rel="其他">其他<small>(1)</small></div>
                
            </li>
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
        
            
        
    
</ul>
<div class="left-bottom">
    <div class="menus">
    
    
    
    <a class="dynamic-menu " target="_blank"  href="https://pgmanor.gitee.io/blog/">pgmanor</a>
    
    
    <a class="dynamic-menu " target="_blank"  href="https://www.iworkh.com/manualIt/Category/scopeDev/">iworkh</a>
    
    
    <a class="dynamic-menu " target="_blank"  href="https://gitee.com/iworkh">gitee</a>
    
    </div>
    <div><a class="about  hasFriend  site_url"  href="/blog/about">关于</a><a style="width: 50%"  class="friends">友链</a></div>
</div>
<input type="hidden" id="iworkh_site_posts_number" value="85">
<input type="hidden" id="iworkh_site_word_count" value="197.8k">
<div style="display: none">
    <span id="busuanzi_value_site_uv"></span>
    <span id="busuanzi_value_site_pv"></span>
</div>

    </div>
    <div class="nav-right">
        <div class="friends-area">
    <div class="friends-title">
        友情链接
        <i class="back-title-list"></i>
    </div>
    <div class="friends-content">
        <ul>
            
            <li><a target="_blank" href="https://www.iworkh.com">iworkh</a></li>
            
            <li><a target="_blank" href="https://www.iworkh.com/download/share/">下载地址</a></li>
            
            <li><a target="_blank" href="http://yelog.org/">叶落阁</a></li>
            
        </ul>
    </div>
</div>
        <div class="title-list">
    <form onkeydown="if(event.keyCode === 13){return false;}">
        <input id="local-search-input" class="search" type="text" placeholder="in: 开头全文搜索" />
        <i class="cross"></i>
        <span>
            <label for="tagswitch">T:</label>
            <input id="tagswitch" type="checkbox" style="display: none" />
            <i id="tagsWitchIcon"></i>
        </span>
    </form>
    <div class="tags-list">
    
    <li class="article-tag-list-item">
        <a class="color3">ai</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">attr</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">aysnc</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">beanmap</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">bigdecimal</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">blog</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">chrome</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">dict</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">dll</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">docker</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">domain</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">fastjson</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">func</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">git</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">github</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">hadoop</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">hexo</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">idea</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">iworkh</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">jackson</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">java</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">jdni</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">jfinalshell</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">joplin</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">JPA</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">lock</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">markdowm</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">maven</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">mock</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">mq</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">mycat</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">mysql</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">nginx</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">notebook</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">plan</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">powermock</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color2">python</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">redis</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">rest</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">sharding-jdbc</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">spark</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">springboot</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">test</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">tool</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">tools</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">tornado</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">transactional</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">web</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">windows</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">wtforms</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">zookeeper</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">分库分表</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">分页</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">命令</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">工具</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">并发</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">序列化</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">微服务</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">性能分析</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">手册</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color4">有道云</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">标签</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">模板</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color5">百度云盘</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color1">结构和算法</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">计划</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">迁移</a>
    </li>
    
    <li class="article-tag-list-item">
        <a class="color3">面试</a>
    </li>
    
    <div class="clearfix"></div>
</div>

    
    <div id="local-search-result">

    </div>
    
    <nav id="title-list-nav">
        
        <a id="top" class="手册 pgmanor "
           href="/blog/2020/12/15/redirect-to-pgmanor/"
           data-tag="迁移"
           data-author="沐雨云楼" >
            <span class="post-title" title="😘博客迁移">😘博客迁移</span>
            <span class="post-date" title="2020-12-15 23:47:55">2020/12/15</span>
        </a>
        
        <a id="top" class="手册 iworkh "
           href="/blog/2020/05/27/manual-iworkh/"
           data-tag="手册,iworkh"
           data-author="沐雨云楼" >
            <span class="post-title" title="iworkh所有手册">iworkh所有手册</span>
            <span class="post-date" title="2020-05-27 21:37:04">2020/05/27</span>
        </a>
        
        <a id="top" class="微服务 springboot "
           href="/blog/2020/06/27/spring_boot_all_resources/"
           data-tag="springboot"
           data-author="沐雨云楼" >
            <span class="post-title" title="Spring Boot全网优质教程汇总">Spring Boot全网优质教程汇总</span>
            <span class="post-date" title="2020-06-27 17:43:13">2020/06/27</span>
        </a>
        
        <a id="top" class="工具 "
           href="/blog/2020/06/22/baidu-pandisk-share/"
           data-tag="百度云盘"
           data-author="沐雨云楼" >
            <span class="post-title" title="百度云盘分享链接">百度云盘分享链接</span>
            <span class="post-date" title="2020-06-22 18:38:21">2020/06/22</span>
        </a>
        
        <a id="top" class="工具 "
           href="/blog/2020/06/21/tool_blogs_all/"
           data-tag="blog"
           data-author="沐雨云楼" >
            <span class="post-title" title="优质博客和文章">优质博客和文章</span>
            <span class="post-date" title="2020-06-21 20:02:14">2020/06/21</span>
        </a>
        
        <a id="top" class="java jvm "
           href="/blog/2020/06/04/java-online-analysis/"
           data-tag="性能分析"
           data-author="沐雨云楼" >
            <span class="post-title" title="JAVA线上故障排查全套路">JAVA线上故障排查全套路</span>
            <span class="post-date" title="2020-06-04 20:34:05">2020/06/04</span>
        </a>
        
        <a id="top" class="工具 idea "
           href="/blog/2020/05/31/jetbrains-idea/"
           data-tag="工具,idea"
           data-author="沐雨云楼" >
            <span class="post-title" title="idea使用技巧">idea使用技巧</span>
            <span class="post-date" title="2020-05-31 16:12:30">2020/05/31</span>
        </a>
        
        <a id="top" class="工具 有道云 "
           href="/blog/2020/05/18/youdao_config_edit/"
           data-tag="工具,有道云"
           data-author="沐雨云楼" >
            <span class="post-title" title="有道云配置修改">有道云配置修改</span>
            <span class="post-date" title="2020-05-18 21:37:04">2020/05/18</span>
        </a>
        
        <a id="top" class="web "
           href="/blog/2020/06/01/web-tools/"
           data-tag="web,tool"
           data-author="沐雨云楼" >
            <span class="post-title" title="前端常用工具">前端常用工具</span>
            <span class="post-date" title="2020-06-01 21:37:04">2020/06/01</span>
        </a>
        
        <a  class="工具 "
           href="/blog/2020/10/25/tool-linux-jfinalshell/"
           data-tag="jfinalshell"
           data-author="沐雨云楼" >
            <span class="post-title" title="linux连接工具jfinalshell">linux连接工具jfinalshell</span>
            <span class="post-date" title="2020-10-25 10:24:59">2020/10/25</span>
        </a>
        
        <a  class="微服务 springboot "
           href="/blog/2020/10/06/spring-boot-multi-profiles/"
           data-tag="springboot"
           data-author="沐雨云楼" >
            <span class="post-title" title="springboot多环境配置打包">springboot多环境配置打包</span>
            <span class="post-date" title="2020-10-06 21:36:42">2020/10/06</span>
        </a>
        
        <a  class="web vue "
           href="/blog/2020/10/06/vue_springboot_package_publish/"
           data-tag="maven"
           data-author="沐雨云楼" >
            <span class="post-title" title="vue+springboot打包发布">vue+springboot打包发布</span>
            <span class="post-date" title="2020-10-06 14:22:29">2020/10/06</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/10/06/java-jpa-query/"
           data-tag="JPA"
           data-author="沐雨云楼" >
            <span class="post-title" title="JPA自定义sql">JPA自定义sql</span>
            <span class="post-date" title="2020-10-06 10:20:38">2020/10/06</span>
        </a>
        
        <a  class="微服务 spring "
           href="/blog/2020/10/03/spring-pageable/"
           data-tag="分页"
           data-author="沐雨云楼" >
            <span class="post-title" title="vue+springboot分页交互">vue+springboot分页交互</span>
            <span class="post-date" title="2020-10-03 11:08:21">2020/10/03</span>
        </a>
        
        <a  class="工具 "
           href="/blog/2020/10/02/chrome-plugin/"
           data-tag="chrome"
           data-author="沐雨云楼" >
            <span class="post-title" title="chrome插件">chrome插件</span>
            <span class="post-date" title="2020-10-02 10:11:31">2020/10/02</span>
        </a>
        
        <a  class="windows "
           href="/blog/2020/10/01/windows-less-dll/"
           data-tag="dll"
           data-author="沐雨云楼" >
            <span class="post-title" title="windows缺少dll">windows缺少dll</span>
            <span class="post-date" title="2020-10-01 20:22:28">2020/10/01</span>
        </a>
        
        <a  class="java "
           href="/blog/2020/09/13/java-base-bigdecimal/"
           data-tag="bigdecimal"
           data-author="沐雨云楼" >
            <span class="post-title" title="BigDecimal正确使用了吗?">BigDecimal正确使用了吗?</span>
            <span class="post-date" title="2020-09-13 10:28:54">2020/09/13</span>
        </a>
        
        <a  class="windows "
           href="/blog/2020/09/12/windows-dev-install/"
           data-tag="windows"
           data-author="沐雨云楼" >
            <span class="post-title" title="windows开发环境安装">windows开发环境安装</span>
            <span class="post-date" title="2020-09-12 21:39:01">2020/09/12</span>
        </a>
        
        <a  class="工具 "
           href="/blog/2020/09/06/tool-notebook-joplin/"
           data-tag="joplin"
           data-author="沐雨云楼" >
            <span class="post-title" title="joplin笔记">joplin笔记</span>
            <span class="post-date" title="2020-09-06 20:50:55">2020/09/06</span>
        </a>
        
        <a  class="web vue "
           href="/blog/2020/09/05/vue-element-admin/"
           data-tag="web"
           data-author="沐雨云楼" >
            <span class="post-title" title="vue element admin开发">vue element admin开发</span>
            <span class="post-date" title="2020-09-05 11:01:38">2020/09/05</span>
        </a>
        
        <a  class="工具 "
           href="/blog/2020/09/05/tool-notebook/"
           data-tag="notebook"
           data-author="沐雨云楼" >
            <span class="post-title" title="常见笔记软件">常见笔记软件</span>
            <span class="post-date" title="2020-09-05 10:54:34">2020/09/05</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/08/30/java-dao-shardingSphere/"
           data-tag="sharding-jdbc"
           data-author="沐雨云楼" >
            <span class="post-title" title="分库分表中间件-ShardingSphere">分库分表中间件-ShardingSphere</span>
            <span class="post-date" title="2020-08-30 21:52:45">2020/08/30</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/08/30/java-dao-mycat/"
           data-tag="mycat"
           data-author="沐雨云楼" >
            <span class="post-title" title="分库分表中间件-MyCat">分库分表中间件-MyCat</span>
            <span class="post-date" title="2020-08-30 15:01:59">2020/08/30</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/08/30/java-dao-sharding-db/"
           data-tag="分库分表"
           data-author="沐雨云楼" >
            <span class="post-title" title="分库分表中间件-开源产品">分库分表中间件-开源产品</span>
            <span class="post-date" title="2020-08-30 09:37:18">2020/08/30</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/08/29/java-jndi/"
           data-tag="jdni"
           data-author="沐雨云楼" >
            <span class="post-title" title="JNDI">JNDI</span>
            <span class="post-date" title="2020-08-29 17:12:03">2020/08/29</span>
        </a>
        
        <a  class="工具 git "
           href="/blog/2020/08/27/tool-git-setting/"
           data-tag="git"
           data-author="沐雨云楼" >
            <span class="post-title" title="git配置">git配置</span>
            <span class="post-date" title="2020-08-27 21:30:17">2020/08/27</span>
        </a>
        
        <a  class="java test "
           href="/blog/2020/08/23/java-powermock/"
           data-tag="powermock"
           data-author="沐雨云楼" >
            <span class="post-title" title="powermock测试框架">powermock测试框架</span>
            <span class="post-date" title="2020-08-23 12:45:17">2020/08/23</span>
        </a>
        
        <a  class="java test "
           href="/blog/2020/08/23/java-mockito/"
           data-tag="mock"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍mockito测试框架">👍mockito测试框架</span>
            <span class="post-date" title="2020-08-23 10:45:17">2020/08/23</span>
        </a>
        
        <a  class="java test "
           href="/blog/2020/08/22/java-test-ng/"
           data-tag="test"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍TestNG一篇足以">👍TestNG一篇足以</span>
            <span class="post-date" title="2020-08-22 18:37:28">2020/08/22</span>
        </a>
        
        <a  class="db mysql "
           href="/blog/2020/08/19/mysql-common-sql/"
           data-tag="mysql"
           data-author="沐雨云楼" >
            <span class="post-title" title="mysql常用sql">mysql常用sql</span>
            <span class="post-date" title="2020-08-19 21:30:00">2020/08/19</span>
        </a>
        
        <a  class="db mysql "
           href="/blog/2020/08/19/mysql-windows-zip-install/"
           data-tag="mysql"
           data-author="沐雨云楼" >
            <span class="post-title" title="mysql zip在windows上安装">mysql zip在windows上安装</span>
            <span class="post-date" title="2020-08-19 21:00:47">2020/08/19</span>
        </a>
        
        <a  class="工具 idea "
           href="/blog/2020/08/15/jetbrains-crack/"
           data-tag="工具,idea"
           data-author="沐雨云楼" >
            <span class="post-title" title="jetbrains全家桶破解">jetbrains全家桶破解</span>
            <span class="post-date" title="2020-08-15 19:22:33">2020/08/15</span>
        </a>
        
        <a  class="工具 git "
           href="/blog/2020/07/10/tool-git-error-name-too-long/"
           data-tag="git"
           data-author="沐雨云楼" >
            <span class="post-title" title="git文件名太长">git文件名太长</span>
            <span class="post-date" title="2020-07-10 12:03:02">2020/07/10</span>
        </a>
        
        <a  class="db mysql "
           href="/blog/2020/07/04/mysql-lock-unlock-table/"
           data-tag="lock"
           data-author="沐雨云楼" >
            <span class="post-title" title="mysql数据库锁查询和释放">mysql数据库锁查询和释放</span>
            <span class="post-date" title="2020-07-04 21:46:54">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-sort/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法8-排序">数据结构和算法8-排序</span>
            <span class="post-date" title="2020-07-04 18:49:12">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-search/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法7-搜索">数据结构和算法7-搜索</span>
            <span class="post-date" title="2020-07-04 17:48:19">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-graph/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法6-非线性-图">数据结构和算法6-非线性-图</span>
            <span class="post-date" title="2020-07-04 16:09:47">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-tree/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法5-非线性-树">数据结构和算法5-非线性-树</span>
            <span class="post-date" title="2020-07-04 15:09:47">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-linear-queue/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法4-线性-队列">数据结构和算法4-线性-队列</span>
            <span class="post-date" title="2020-07-04 14:10:22">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-linear-stack/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法3-线性-栈">数据结构和算法3-线性-栈</span>
            <span class="post-date" title="2020-07-04 13:00:17">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-linear-list/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法2-线性-链">数据结构和算法2-线性-链</span>
            <span class="post-date" title="2020-07-04 11:32:12">2020/07/04</span>
        </a>
        
        <a  class="结构和算法 "
           href="/blog/2020/07/04/data-structures-algorithms-info/"
           data-tag="结构和算法"
           data-author="沐雨云楼" >
            <span class="post-title" title="数据结构和算法1-介绍">数据结构和算法1-介绍</span>
            <span class="post-date" title="2020-07-04 10:10:40">2020/07/04</span>
        </a>
        
        <a  class="大数据 spark "
           href="/blog/2020/06/30/spark-install-windows/"
           data-tag="spark"
           data-author="沐雨云楼" >
            <span class="post-title" title="spark开发环境搭建">spark开发环境搭建</span>
            <span class="post-date" title="2020-06-30 19:24:51">2020/06/30</span>
        </a>
        
        <a  class="大数据 hadoop "
           href="/blog/2020/06/30/hadoop-install-windows/"
           data-tag="hadoop"
           data-author="沐雨云楼" >
            <span class="post-title" title="hadoop开发环境搭建">hadoop开发环境搭建</span>
            <span class="post-date" title="2020-06-30 18:06:23">2020/06/30</span>
        </a>
        
        <a  class="java "
           href="/blog/2020/06/30/java-install-windows/"
           data-tag="java"
           data-author="沐雨云楼" >
            <span class="post-title" title="jdk开发环境搭建">jdk开发环境搭建</span>
            <span class="post-date" title="2020-06-30 15:09:34">2020/06/30</span>
        </a>
        
        <a  class="ai "
           href="/blog/2020/06/30/ai-python-spark-env/"
           data-tag="spark"
           data-author="沐雨云楼" >
            <span class="post-title" title="python spark开发环境搭建">python spark开发环境搭建</span>
            <span class="post-date" title="2020-06-30 12:14:04">2020/06/30</span>
        </a>
        
        <a  class="ai "
           href="/blog/2020/06/29/ai-jupyter-notebook/"
           data-tag="标签"
           data-author="沐雨云楼" >
            <span class="post-title" title="jupyter notebook">jupyter notebook</span>
            <span class="post-date" title="2020-06-29 12:29:36">2020/06/29</span>
        </a>
        
        <a  class="ai "
           href="/blog/2020/06/26/ai-python-tools/"
           data-tag="tools"
           data-author="沐雨云楼" >
            <span class="post-title" title="python和ai常用工具">python和ai常用工具</span>
            <span class="post-date" title="2020-06-26 09:55:24">2020/06/26</span>
        </a>
        
        <a  class="python "
           href="/blog/2020/06/25/python-tool-buildin-attr/"
           data-tag="attr"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍python内置属性(魔法方法)">👍python内置属性(魔法方法)</span>
            <span class="post-date" title="2020-06-25 11:01:33">2020/06/25</span>
        </a>
        
        <a  class="python "
           href="/blog/2020/06/24/python-tool-buildin-func/"
           data-tag="func"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍python内置函数">👍python内置函数</span>
            <span class="post-date" title="2020-06-24 18:31:59">2020/06/24</span>
        </a>
        
        <a  class="python "
           href="/blog/2020/06/24/python-tool-obj-dict/"
           data-tag="dict"
           data-author="沐雨云楼" >
            <span class="post-title" title="python对象与dict互转">python对象与dict互转</span>
            <span class="post-date" title="2020-06-24 18:15:03">2020/06/24</span>
        </a>
        
        <a  class="大数据 spark "
           href="/blog/2020/06/21/spark-version-3/"
           data-tag="spark"
           data-author="沐雨云楼" >
            <span class="post-title" title="spark3.0新特性">spark3.0新特性</span>
            <span class="post-date" title="2020-06-21 17:40:12">2020/06/21</span>
        </a>
        
        <a  class="java "
           href="/blog/2020/06/21/java-base-domain/"
           data-tag="domain"
           data-author="沐雨云楼" >
            <span class="post-title" title="VO DTO DO PO你都了解吗?">VO DTO DO PO你都了解吗?</span>
            <span class="post-date" title="2020-06-21 09:20:03">2020/06/21</span>
        </a>
        
        <a  class="大数据 spark "
           href="/blog/2020/06/20/spark-data-skew/"
           data-tag="spark"
           data-author="沐雨云楼" >
            <span class="post-title" title="spark数据倾斜及解决方案">spark数据倾斜及解决方案</span>
            <span class="post-date" title="2020-06-20 21:41:12">2020/06/20</span>
        </a>
        
        <a  class="linux nginx "
           href="/blog/2020/06/20/nginx-fast/"
           data-tag="nginx"
           data-author="沐雨云楼" >
            <span class="post-title" title="nginx为何这么快?">nginx为何这么快?</span>
            <span class="post-date" title="2020-06-20 17:55:10">2020/06/20</span>
        </a>
        
        <a  class="架构 "
           href="/blog/2020/06/20/fw-mq-message-missing/"
           data-tag="mq"
           data-author="沐雨云楼" >
            <span class="post-title" title="MQ如何保证消息不丢失?">MQ如何保证消息不丢失?</span>
            <span class="post-date" title="2020-06-20 17:03:18">2020/06/20</span>
        </a>
        
        <a  class="java "
           href="/blog/2020/06/20/java-base-serializable/"
           data-tag="序列化"
           data-author="沐雨云楼" >
            <span class="post-title" title="java序列化">java序列化</span>
            <span class="post-date" title="2020-06-20 16:29:04">2020/06/20</span>
        </a>
        
        <a  class="架构 "
           href="/blog/2020/06/20/fw-distributed-lock-redis-zk/"
           data-tag="lock,redis,zookeeper"
           data-author="沐雨云楼" >
            <span class="post-title" title="分布式锁用redis还是zk?">分布式锁用redis还是zk?</span>
            <span class="post-date" title="2020-06-20 12:09:55">2020/06/20</span>
        </a>
        
        <a  class="java tools "
           href="/blog/2020/06/19/java-tool-jackson/"
           data-tag="jackson"
           data-author="沐雨云楼" >
            <span class="post-title" title="工具类--jackson">工具类--jackson</span>
            <span class="post-date" title="2020-06-19 13:00:45">2020/06/19</span>
        </a>
        
        <a  class="java tools "
           href="/blog/2020/06/19/java-tool-bean-map/"
           data-tag="beanmap"
           data-author="沐雨云楼" >
            <span class="post-title" title="工具类--bean和map互转">工具类--bean和map互转</span>
            <span class="post-date" title="2020-06-19 12:30:14">2020/06/19</span>
        </a>
        
        <a  class="java test "
           href="/blog/2020/06/18/java-rest-assured-in-action/"
           data-tag="rest"
           data-author="沐雨云楼" >
            <span class="post-title" title="rest-assured实战">rest-assured实战</span>
            <span class="post-date" title="2020-06-18 17:51:56">2020/06/18</span>
        </a>
        
        <a  class="java test "
           href="/blog/2020/06/17/java_rest_assured_wiki_info/"
           data-tag="rest"
           data-author="沐雨云楼" >
            <span class="post-title" title="rest-assured wiki翻译">rest-assured wiki翻译</span>
            <span class="post-date" title="2020-06-17 20:39:11">2020/06/17</span>
        </a>
        
        <a  class="python tornado "
           href="/blog/2020/06/16/tornado-wtforms/"
           data-tag="wtforms"
           data-author="沐雨云楼" >
            <span class="post-title" title="wtforms表单验证">wtforms表单验证</span>
            <span class="post-date" title="2020-06-16 21:52:33">2020/06/16</span>
        </a>
        
        <a  class="python tornado "
           href="/blog/2020/06/16/tornado-sync-to-async/"
           data-tag="aysnc"
           data-author="沐雨云楼" >
            <span class="post-title" title="tornado同步转异步">tornado同步转异步</span>
            <span class="post-date" title="2020-06-16 21:50:25">2020/06/16</span>
        </a>
        
        <a  class="python tornado "
           href="/blog/2020/06/08/python_tornado_info/"
           data-tag="tornado"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍Tornado入门这一篇足以">👍Tornado入门这一篇足以</span>
            <span class="post-date" title="2020-06-08 20:31:32">2020/06/08</span>
        </a>
        
        <a  class="工具 maven "
           href="/blog/2020/06/07/tool-maven/"
           data-tag="tools,工具"
           data-author="沐雨云楼" >
            <span class="post-title" title="maven常用命令和配置">maven常用命令和配置</span>
            <span class="post-date" title="2020-06-07 16:30:11">2020/06/07</span>
        </a>
        
        <a  class="微服务 springboot "
           href="/blog/2020/06/07/spring-initialzr/"
           data-tag="tools,工具"
           data-author="沐雨云楼" >
            <span class="post-title" title="spring initialzr环境编译发布">spring initialzr环境编译发布</span>
            <span class="post-date" title="2020-06-07 15:06:00">2020/06/07</span>
        </a>
        
        <a  class="windows "
           href="/blog/2020/06/07/windows-cmd/"
           data-tag="tools,工具,命令"
           data-author="沐雨云楼" >
            <span class="post-title" title="windows常用命令和技巧">windows常用命令和技巧</span>
            <span class="post-date" title="2020-06-07 11:39:01">2020/06/07</span>
        </a>
        
        <a  class="工具 github "
           href="/blog/2020/06/07/tool-connect-github/"
           data-tag="github"
           data-author="沐雨云楼" >
            <span class="post-title" title="github无法访问解决办法">github无法访问解决办法</span>
            <span class="post-date" title="2020-06-07 10:08:38">2020/06/07</span>
        </a>
        
        <a  class="微服务 spring "
           href="/blog/2020/06/03/spring-transaction/"
           data-tag="transactional"
           data-author="沐雨云楼" >
            <span class="post-title" title="Transactional注解">Transactional注解</span>
            <span class="post-date" title="2020-06-03 22:09:42">2020/06/03</span>
        </a>
        
        <a  class="java dao "
           href="/blog/2020/06/03/java-jpa-query-multi-param/"
           data-tag="JPA"
           data-author="沐雨云楼" >
            <span class="post-title" title="JPA多条件查询">JPA多条件查询</span>
            <span class="post-date" title="2020-06-03 21:38:04">2020/06/03</span>
        </a>
        
        <a  class="java tools "
           href="/blog/2020/06/02/java-tool-fastjson-bug/"
           data-tag="fastjson"
           data-author="沐雨云楼" >
            <span class="post-title" title="Fastjson远程代码高危漏洞">Fastjson远程代码高危漏洞</span>
            <span class="post-date" title="2020-06-02 22:38:28">2020/06/02</span>
        </a>
        
        <a  class="linux docker "
           href="/blog/2020/06/02/docker-introduction/"
           data-tag="docker"
           data-author="沐雨云楼" >
            <span class="post-title" title="👍docker入门看这一篇就够了">👍docker入门看这一篇就够了</span>
            <span class="post-date" title="2020-06-02 22:22:30">2020/06/02</span>
        </a>
        
        <a  class="面试 "
           href="/blog/2020/06/02/interview-java-question/"
           data-tag="面试"
           data-author="沐雨云楼" >
            <span class="post-title" title="java常见面试题">java常见面试题</span>
            <span class="post-date" title="2020-06-02 22:15:48">2020/06/02</span>
        </a>
        
        <a  class="微服务 "
           href="/blog/2020/06/02/microservice-why/"
           data-tag="微服务"
           data-author="沐雨云楼" >
            <span class="post-title" title="牛逼公司一定要使用微服务？">牛逼公司一定要使用微服务？</span>
            <span class="post-date" title="2020-06-02 21:15:29">2020/06/02</span>
        </a>
        
        <a  class="面试 "
           href="/blog/2020/06/02/interview-company/"
           data-tag="面试"
           data-author="沐雨云楼" >
            <span class="post-title" title="IT互联网公司名单">IT互联网公司名单</span>
            <span class="post-date" title="2020-06-02 20:41:23">2020/06/02</span>
        </a>
        
        <a  class="java 并发 "
           href="/blog/2020/06/02/java-concurrent-aqs/"
           data-tag="java,并发"
           data-author="沐雨云楼" >
            <span class="post-title" title="java并发AQS">java并发AQS</span>
            <span class="post-date" title="2020-06-02 20:01:48">2020/06/02</span>
        </a>
        
        <a  class="ai "
           href="/blog/2020/05/30/ai-learn-catalog/"
           data-tag="ai"
           data-author="沐雨云楼" >
            <span class="post-title" title="AI学习目录">AI学习目录</span>
            <span class="post-date" title="2020-05-30 08:49:39">2020/05/30</span>
        </a>
        
        <a  class="工具 hexo "
           href="/blog/2020/05/29/hexo-theme-math/"
           data-tag="工具,hexo"
           data-author="沐雨云楼" >
            <span class="post-title" title="hexo主题支持MathJax">hexo主题支持MathJax</span>
            <span class="post-date" title="2020-05-29 09:36:46">2020/05/29</span>
        </a>
        
        <a  class="db redis "
           href="/blog/2020/05/28/redis-memory-eliminate/"
           data-tag="redis"
           data-author="沐雨云楼" >
            <span class="post-title" title="Redis的内存淘汰策略">Redis的内存淘汰策略</span>
            <span class="post-date" title="2020-05-28 21:37:04">2020/05/28</span>
        </a>
        
        <a  class="工具 markdowm "
           href="/blog/2020/05/28/markdown-math-express/"
           data-tag="工具,markdowm"
           data-author="沐雨云楼" >
            <span class="post-title" title="markdown数学公式">markdown数学公式</span>
            <span class="post-date" title="2020-05-28 08:02:03">2020/05/28</span>
        </a>
        
        <a  class="python "
           href="/blog/2020/05/21/python_install/"
           data-tag="python"
           data-author="沐雨云楼" >
            <span class="post-title" title="python环境安装">python环境安装</span>
            <span class="post-date" title="2020-05-21 21:37:04">2020/05/21</span>
        </a>
        
        <a  class="python "
           href="/blog/2020/05/21/python_base/"
           data-tag="python"
           data-author="沐雨云楼" >
            <span class="post-title" title="python基本语法">python基本语法</span>
            <span class="post-date" title="2020-05-21 00:00:00">2020/05/21</span>
        </a>
        
        <a  class="其他 "
           href="/blog/2020/05/20/todo-list/"
           data-tag="plan,计划"
           data-author="沐雨云楼" >
            <span class="post-title" title="计划">计划</span>
            <span class="post-date" title="2020-05-20 11:04:19">2020/05/20</span>
        </a>
        
        <a  class="工具 "
           href="/blog/2020/05/20/blog-template/"
           data-tag="模板"
           data-author="沐雨云楼" >
            <span class="post-title" title="博客自定义模板">博客自定义模板</span>
            <span class="post-date" title="2020-05-20 05:20:00">2020/05/20</span>
        </a>
        
    </nav>
</div>

    </div>
    <div class="hide-list">
        <div class="semicircle" data-title="切换全屏 快捷键 s">
            <div class="brackets first"><</div>
            <div class="brackets">&gt;</div>
        </div>
    </div>
</aside>
<div class="post">
    <div class="pjax">
        <article id="post-mysql-common-sql" class="article article-type-post" itemscope itemprop="blogPost">
    
        <h1 class="article-title">mysql常用sql</h1>
    
    <div class="article-meta">
        
        
        <span class="author"><a>沐雨云楼</a></span>
        
        
        <span class="book">
            
                <a  data-rel="db">db</a>/
            
                <a  data-rel="mysql">mysql</a>
            
        </span>
        
        
        <span class="tag">
            
            <a class="color1">mysql</a>
            
        </span>
        
    </div>
    <div class="article-meta">
        
            发布时间 : <time class="date" title='最后更新: 2020-10-06 10:20:38'>2020-08-19 21:30</time>
        
    </div>
    <div class="article-meta">
        
        <span>字数:4.4k</span>
        
        
        <span id="busuanzi_container_page_pv">
            阅读 :<span id="busuanzi_value_page_pv">
                <span class="count-comment">
                    <span class="spinner">
                      <div class="cube1"></div>
                      <div class="cube2"></div>
                    </span>
                </span>
            </span>
        </span>
        
        
        <span class="top-comment" title="跳转至评论区">
            <a href="#comments">
                评论:<span class="count-comment">
                    <span class="spinner">
                      <div class="cube1"></div>
                      <div class="cube2"></div>
                    </span>
                </span>
            </a>
        </span>
        
    </div>
    
    <div class="toc-ref">
    
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-准备工作"><span class="toc-text">1.准备工作</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-1-建库语句"><span class="toc-text">1-1.建库语句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#1-2-建表语句"><span class="toc-text">1-2.建表语句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#1-3-初始化数据"><span class="toc-text">1-3.初始化数据</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-常用sql"><span class="toc-text">2.常用sql</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#2-1-建表"><span class="toc-text">2-1.建表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-2-修改表"><span class="toc-text">2-2.修改表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-3-索引操作"><span class="toc-text">2-3.索引操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-4-其他"><span class="toc-text">2-4.其他</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-explain"><span class="toc-text">3.explain</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#3-1-介绍"><span class="toc-text">3-1.介绍</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-2-示例"><span class="toc-text">3-2.示例</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-常用函数"><span class="toc-text">4.常用函数</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#4-1-日期"><span class="toc-text">4-1.日期</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-2-coalesce"><span class="toc-text">4-2.coalesce</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-链接"><span class="toc-text">5.链接</span></a></li></ol>
    
<style>
    .left-col .switch-btn,
    .left-col .switch-area {
        display: none;
    }
    .toc-level-4 i,
    .toc-level-4 ol {
        display: none !important;
    }
</style>
</div>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="1-准备工作"><a href="#1-准备工作" class="headerlink" title="1.准备工作"></a>1.准备工作</h2><p>模仿oracel的几张默认表，进行演示。所以下面先建表和导入数据。</p>
<h3 id="1-1-建库语句"><a href="#1-1-建库语句" class="headerlink" title="1-1.建库语句"></a>1-1.建库语句</h3><pre><code class="sql">CREATE DATABASE IF NOT EXISTS iworkh_demo
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_general_ci;</code></pre>
<pre><code class="sql">-- 数据库编码格式
show variables like &#39;character_set_database&#39;;

-- 查看数据表的编码格式
show create table &lt;表名&gt;;

-- 修改数据库的编码格式
ALTER DATABASE &lt;数据库名&gt; character set utf8mb4;
</code></pre>
<h3 id="1-2-建表语句"><a href="#1-2-建表语句" class="headerlink" title="1-2.建表语句"></a>1-2.建表语句</h3><p><strong>dept表</strong></p>
<pre><code class="sql">create table dept
(
    deptno int unsigned auto_increment primary key COMMENT &#39;部门编号&#39;,
    dname  varchar(15) COMMENT &#39;部门名称&#39;,
    loc    varchar(50) COMMENT &#39;部门所在位置&#39;
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT =&#39;部门表&#39;;</code></pre>
<p><strong>emp表</strong></p>
<pre><code class="sql">create table emp
(
    empno    int unsigned auto_increment primary key COMMENT &#39;雇员编号&#39;,
    ename    varchar(15) COMMENT &#39;雇员姓名&#39;,
    job      varchar(10) COMMENT &#39;雇员职位&#39;,
    mgr      int unsigned COMMENT &#39;雇员对应的领导的编号&#39;,
    hiredate date COMMENT &#39;雇员的雇佣日期&#39;,
    sal      decimal(7, 2) COMMENT &#39;雇员的基本工资&#39;,
    comm     decimal(7, 2) COMMENT &#39;奖金&#39;,
    deptno   int unsigned COMMENT &#39;所在部门&#39;,
    foreign key (deptno) references dept (deptno)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT =&#39;雇员表&#39;;</code></pre>
<p><strong>salgrade表,工资等级表</strong></p>
<pre><code class="sql">create table salgrade
(
    grade int unsigned COMMENT &#39;工资等级&#39;,
    losal int unsigned COMMENT &#39;此等级的最低工资&#39;,
    hisal int unsigned COMMENT &#39;此等级的最高工资&#39;
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT =&#39;工资等级表&#39;;</code></pre>
<p><strong>bonus 表,工资表</strong></p>
<pre><code class="sql">create table bonus
(
    ename varchar(10) COMMENT &#39;雇员姓名&#39;,
    job   varchar(9) COMMENT &#39;雇员职位&#39;,
    sal   decimal(7, 2) COMMENT &#39;雇员工资&#39;,
    comm  decimal(7, 2) COMMENT &#39;雇员资金&#39;
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT =&#39;工资表&#39;</code></pre>
<h3 id="1-3-初始化数据"><a href="#1-3-初始化数据" class="headerlink" title="1-3.初始化数据"></a>1-3.初始化数据</h3><p><strong>dept的初始化数据</strong></p>
<pre><code class="sql">INSERT INTO dept
VALUES (10, &#39;ACCOUNTING&#39;, &#39;NEW YORK&#39;);
INSERT INTO dept
VALUES (20, &#39;RESEARCH&#39;, &#39;DALLAS&#39;);
INSERT INTO dept
VALUES (30, &#39;SALES&#39;, &#39;CHICAGO&#39;);
INSERT INTO dept
VALUES (40, &#39;OPERATIONS&#39;, &#39;BOSTON&#39;);</code></pre>
<p><strong>emp的初始数据</strong></p>
<pre><code class="sql">INSERT INTO emp
VALUES (7369, &#39;SMITH&#39;, &#39;CLERK&#39;, 7902, &#39;1980-12-17&#39;, 800, NULL, 20);
INSERT INTO emp
VALUES (7499, &#39;ALLEN&#39;, &#39;SALESMAN&#39;, 7698, &#39;1981-2-20&#39;, 1600, 300, 30);
INSERT INTO emp
VALUES (7521, &#39;WARD&#39;, &#39;SALESMAN&#39;, 7698, &#39;1981-2-22&#39;, 1250, 500, 30);
INSERT INTO emp
VALUES (7566, &#39;JONES&#39;, &#39;MANAGER&#39;, 7839, &#39;1981-4-2&#39;, 2975, NULL, 20);
INSERT INTO emp
VALUES (7654, &#39;MARTIN&#39;, &#39;SALESMAN&#39;, 7698, &#39;1981-9-28&#39;, 1250, 1400, 30);
INSERT INTO emp
VALUES (7698, &#39;BLAKE&#39;, &#39;MANAGER&#39;, 7839, &#39;1981-5-1&#39;, 2850, NULL, 30);
INSERT INTO emp
VALUES (7782, &#39;CLARK&#39;, &#39;MANAGER&#39;, 7839, &#39;1981-6-9&#39;, 2450, NULL, 10);
INSERT INTO emp
VALUES (7788, &#39;SCOTT&#39;, &#39;ANALYST&#39;, 7566, &#39;87-7-13&#39;, 3000, NULL, 20);
INSERT INTO emp
VALUES (7839, &#39;KING&#39;, &#39;PRESIDENT&#39;, NULL, &#39;1981-11-17&#39;, 5000, NULL, 10);
INSERT INTO emp
VALUES (7844, &#39;TURNER&#39;, &#39;SALESMAN&#39;, 7698, &#39;1981-9-8&#39;, 1500, 0, 30);
INSERT INTO emp
VALUES (7876, &#39;ADAMS&#39;, &#39;CLERK&#39;, 7788, &#39;87-7-13&#39;, 1100, NULL, 20);
INSERT INTO emp
VALUES (7900, &#39;JAMES&#39;, &#39;CLERK&#39;, 7698, &#39;1981-12-3&#39;, 950, NULL, 30);
INSERT INTO emp
VALUES (7902, &#39;FORD&#39;, &#39;ANALYST&#39;, 7566, &#39;1981-12-3&#39;, 3000, NULL, 20);
INSERT INTO emp
VALUES (7934, &#39;MILLER&#39;, &#39;CLERK&#39;, 7782, &#39;1982-1-23&#39;, 1300, NULL, 10);</code></pre>
<p><strong>salgrade 的初始数据</strong></p>
<pre><code class="sql">INSERT INTO salgrade
VALUES (1, 700, 1200);
INSERT INTO salgrade
VALUES (2, 1201, 1400);
INSERT INTO salgrade
VALUES (3, 1401, 2000);
INSERT INTO salgrade
VALUES (4, 2001, 3000);
INSERT INTO salgrade
VALUES (5, 3001, 9999);</code></pre>
<h2 id="2-常用sql"><a href="#2-常用sql" class="headerlink" title="2.常用sql"></a>2.常用sql</h2><h3 id="2-1-建表"><a href="#2-1-建表" class="headerlink" title="2-1.建表"></a>2-1.建表</h3><p><strong>建表并定义索引</strong></p>
<pre><code class="sql">CREATE TABLE projectfile
(
    id               INT AUTO_INCREMENT COMMENT &#39;附件id&#39;,
    fileuploadercode VARCHAR(128) COMMENT &#39;附件上传者code&#39;,
    projectid        INT COMMENT &#39;项目id;此列受project表中的id列约束&#39;,
    filename         VARCHAR(512) COMMENT &#39;附件名&#39;,
    fileurl          VARCHAR(512) COMMENT &#39;附件下载地址&#39;,
    filesize         BIGINT COMMENT &#39;附件大小，单位Byte&#39;,
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
    PRIMARY KEY (id),
    -- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
    FOREIGN KEY (projectid) REFERENCES project (id),
    -- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    UNIQUE INDEX (projectid),
    -- 给fileuploadercode字段创建普通索引
    INDEX (fileuploadercode)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 COMMENT &#39;项目附件表&#39;;</code></pre>
<p><strong>建表并定义组合索引</strong></p>
<pre><code class="sql">CREATE TABLE projectfile
(
    id               INT AUTO_INCREMENT COMMENT &#39;附件id&#39;,
    fileuploadercode VARCHAR(128) COMMENT &#39;附件上传者code&#39;,
    projectid        INT COMMENT &#39;项目id;此列受project表中的id列约束&#39;,
    filename         VARCHAR(512) COMMENT &#39;附件名&#39;,
    fileurl          VARCHAR(512) COMMENT &#39;附件下载地址&#39;,
    filesize         BIGINT COMMENT &#39;附件大小，单位Byte&#39;,
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
    PRIMARY KEY (id),
    -- 创建组合索引
    INDEX (fileuploadercode, projectid)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 COMMENT &#39;项目附件表&#39;;</code></pre>
<h3 id="2-2-修改表"><a href="#2-2-修改表" class="headerlink" title="2-2.修改表"></a>2-2.修改表</h3><p><strong>添加字段</strong></p>
<pre><code class="sql">ALTER TABLE order ADD code CHAR(6) DEFAULT NULL COMMENT &#39;优惠码&#39;</code></pre>
<p><strong>修改字段</strong></p>
<pre><code class="sql">-- 修改字段名
ALTER TABLE 表名 CHANGE 旧字段名  新字段名

-- 修改字段类型
ALTER TABLE 表名 MODIFY 字段名 字段类型(字段长度)</code></pre>
<p><strong>删除字段</strong></p>
<pre><code class="sql">ALTER TABLE 表名 DROP 字段名 </code></pre>
<h3 id="2-3-索引操作"><a href="#2-3-索引操作" class="headerlink" title="2-3.索引操作"></a>2-3.索引操作</h3><p><strong>查询</strong></p>
<pre><code class="sql">SHOW INDEX FROM `table_name`;</code></pre>
<p><strong>删除</strong></p>
<pre><code class="sql">DROP INDEX `索引名` ON `表名`;

ALTER TABLE `表名` DROP INDEX `索引名`</code></pre>
<p><strong>添加索引</strong></p>
<p>语法：</p>
<pre><code class="sql">ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法];</code></pre>
<p>示例：</p>
<pre><code class="sql">-- 添加PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

-- 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` );

-- 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`);

-- 添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

-- 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`);</code></pre>
<h3 id="2-4-其他"><a href="#2-4-其他" class="headerlink" title="2-4.其他"></a>2-4.其他</h3><pre><code class="sql">-- 查询有哪些表
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = &#39;iworkh_demo&#39;;
</code></pre>
<h2 id="3-explain"><a href="#3-explain" class="headerlink" title="3.explain"></a>3.explain</h2><h3 id="3-1-介绍"><a href="#3-1-介绍" class="headerlink" title="3-1.介绍"></a>3-1.介绍</h3><p>分析性能和对mysql优化时，常常会使用explain，来查看执行计划。</p>
<pre><code class="sql">explain
SELECT ename, hiredate hiredate
FROM emp
WHERE empno = 7499;</code></pre>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>const</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>const</td>
<td>1</td>
<td>100</td>
<td>NULL</td>
</tr>
</tbody></table>
<ul>
<li>id：SELECT识别符。这是SELECT的查询序列号。</li>
<li>select_type：SELECT类型。<blockquote>
<ul>
<li>SIMPLE： 简单SELECT(不使用UNION或子查询)</li>
<li>PRIMARY： 最外面的SELECT</li>
<li>UNION：UNION中的第二个或后面的SELECT语句</li>
<li>DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询</li>
<li>UNION RESULT：UNION的结果</li>
<li>SUBQUERY：子查询中的第一个SELECT</li>
<li>DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询</li>
<li>DERIVED：导出表的SELECT(FROM子句的子查询)</li>
</ul>
</blockquote>
</li>
<li>table：表名</li>
<li>type：联接类型。是SQL性能的非常重要的一个指标，结果值从好到坏依次是：<br><code>system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; index_merge &gt; unique_subquery &gt; index_subquery &gt; range &gt; index &gt; ALL</code>。一般来说，得保证查询至少达到range级别。<blockquote>
<ul>
<li>system：表仅有一行(=系统表)。这是const联接类型的一个特例。</li>
<li>const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。</li>
<li>eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。</li>
<li>ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或&lt;=&gt;操作符的带索引的列。</li>
<li>ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。</li>
<li>index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。</li>
<li>unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。</li>
<li>index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)</li>
<li>range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、&lt;&gt;、&gt;、&gt;=、&lt;、&lt;=、IS NULL、&lt;=&gt;、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range</li>
<li>index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。</li>
<li>all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。</li>
</ul>
</blockquote>
</li>
<li>possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。</li>
<li>key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。</li>
<li>key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。</li>
<li>ref：ref列显示使用哪个列或常数与key一起从表中选择行。</li>
<li>rows：rows列显示MySQL认为它执行查询时必须检查的行数。</li>
<li>extra：该列包含MySQL解决查询的详细信息。<blockquote>
<ul>
<li>Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。</li>
<li>Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。</li>
<li>range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。</li>
<li>Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。</li>
<li>Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。</li>
<li>Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。</li>
<li>Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。</li>
<li>Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。</li>
<li>Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。</li>
</ul>
</blockquote>
</li>
</ul>
<h3 id="3-2-示例"><a href="#3-2-示例" class="headerlink" title="3-2.示例"></a>3-2.示例</h3><p><strong>先查表上有哪些索引</strong></p>
<pre><code class="sql">show index from emp;</code></pre>
<blockquote>
<p>empno: primary key<br>deptno: index</p>
</blockquote>
<p><strong>示例1</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE empno = 7499;</code></pre>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>const</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>const</td>
<td>1</td>
<td>100</td>
<td>NULL</td>
</tr>
</tbody></table>
<p><strong>示例2</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno = 20;

explain SELECT * FROM emp WHERE deptno in (20);</code></pre>
<blockquote>
<p>where条件字段是索引，使用=, type为<code>ref</code>，ref为<code>const</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>ref</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>const</td>
<td>5</td>
<td>100</td>
<td>NULL</td>
</tr>
</tbody></table>
<p><strong>示例3</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno in (10, 20);</code></pre>
<blockquote>
<p>where条件字段是索引，使用in, type为<code>range</code>，extra为<code>Using index condition</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>range</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>NULL</td>
<td>8</td>
<td>100</td>
<td>Using index condition</td>
</tr>
</tbody></table>
<p><strong>示例4</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno in (20, 30);</code></pre>
<blockquote>
<p>where条件字段是索引，使用in, type为<code>ALL</code>，extra为<code>Using where</code>.<br>这in的语句虽然跟上一个差不多，但是<code>in (20, 30)</code>比<code>in (10, 20)</code>的数据量多，优化器会任务全表扫描效率比通过索引快，所以type为<code>ALL</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>ALL</td>
<td>deptno</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>14</td>
<td>78.57</td>
<td>Using where</td>
</tr>
</tbody></table>
<p><strong>那如何让它命中索引呢？</strong></p>
<p><strong>force index</strong></p>
<pre><code class="sql">explain SELECT * FROM emp force index(deptno) WHERE deptno in (20, 30);</code></pre>
<blockquote>
<p>使用<code>force index(deptno)</code>强制性走索引</p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>ALL</td>
<td>deptno</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>14</td>
<td>78.57</td>
<td>Using where</td>
</tr>
</tbody></table>
<p><strong>union all</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno=20 union all SELECT * FROM emp WHERE deptno=30;</code></pre>
<blockquote>
<p>两个union all时select_type为<code>select_type</code>和<code>UNION</code><br>where条件字段是索引, type为<code>ref</code>，ref为<code>const</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>PRIMARY</td>
<td>emp</td>
<td>NULL</td>
<td>ref</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>const</td>
<td>5</td>
<td>100</td>
<td>NULL</td>
</tr>
<tr>
<td>2</td>
<td>UNION</td>
<td>emp</td>
<td>NULL</td>
<td>ref</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>const</td>
<td>6</td>
<td>100</td>
<td>NULL</td>
</tr>
</tbody></table>
<p><strong>union</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno=20 union SELECT * FROM emp WHERE deptno=30;</code></pre>
<blockquote>
<p>两个union all时select_type为<code>select_type</code>、<code>UNION</code>和<code>UNION RESULT</code><br>where条件字段是索引, type为<code>ref</code>，ref为<code>const</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>PRIMARY</td>
<td>emp</td>
<td>NULL</td>
<td>ref</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>const</td>
<td>5</td>
<td>100</td>
<td>NULL</td>
</tr>
<tr>
<td>2</td>
<td>UNION</td>
<td>emp</td>
<td>NULL</td>
<td>ref</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>const</td>
<td>6</td>
<td>100</td>
<td>NULL</td>
</tr>
<tr>
<td>NULL</td>
<td>UNION RESULT</td>
<td>&lt;union1,2&gt;</td>
<td>NULL</td>
<td>ALL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>Using temporary</td>
</tr>
</tbody></table>
<blockquote>
<p>这注意下<code>union</code>和<code>union all</code>的区别。<code>union all</code>不会去重，而<code>union</code>会去重。</p>
</blockquote>
<p><strong>示例5</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE deptno &gt; 20;</code></pre>
<blockquote>
<p>where条件字段是索引, type为<code>range</code>，extra为<code>Using index condition</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>range</td>
<td>deptno</td>
<td>deptno</td>
<td>5</td>
<td>NULL</td>
<td>6</td>
<td>100</td>
<td>Using index condition</td>
</tr>
</tbody></table>
<p><strong>示例6</strong></p>
<pre><code class="sql">explain SELECT * FROM emp WHERE sal &gt; 2000;</code></pre>
<blockquote>
<p>where条件字段不是索引, type为<code>ALL</code>，extra为<code>Using where</code></p>
</blockquote>
<p>结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>partitions</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>fitered</th>
<th>extra</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>SIMPLE</td>
<td>emp</td>
<td>NULL</td>
<td>ALL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>14</td>
<td>33.33</td>
<td>Using where</td>
</tr>
</tbody></table>
<h2 id="4-常用函数"><a href="#4-常用函数" class="headerlink" title="4.常用函数"></a>4.常用函数</h2><ul>
<li><a href="https://www.runoob.com/mysql/mysql-functions.html" target="_blank" rel="noopener">菜鸟教程-mysql</a></li>
<li><a href="https://blog.csdn.net/sinat_38899493/article/details/78710482" target="_blank" rel="noopener">MySql常用函数大全讲解</a></li>
</ul>
<h3 id="4-1-日期"><a href="#4-1-日期" class="headerlink" title="4-1.日期"></a>4-1.日期</h3><p>日期相关常用函数示例</p>
<pre><code class="sql">-- CURRENT_DATE 当前日期  2020-09-22
select CURRENT_DATE() from dual;

-- CURRENT_TIME 当前时间 10:16:00
select CURRENT_TIME() from dual;

-- CURRENT_TIMESTAMP 当前时间 2020-09-22 10:16:54
select CURRENT_TIMESTAMP() from dual;

-- NOW 当前时间 2020-09-22 10:16:54
select NOW() from dual;

-- DATE_ADD (两天前)
select DATE_ADD(NOW(), INTERVAL -2 DAY) from dual;

-- (两天后)
select DATE_ADD(NOW(), INTERVAL 2 DAY) from dual;

-- DATE_FORMAT 日期转化为`YYYY-MM-DD HH:MM:SS`格式
SELECT ename, DATE_FORMAT(hiredate, &#39;%Y-%c-%d %h:%i:%s&#39;) hiredate FROM emp;
SELECT ename, DATE_FORMAT(hiredate, &#39;%Y-%c-%d&#39;) hiredate FROM emp;

-- 复杂点的
select concat(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 DAY),&#39;%Y-%m-%d&#39;) ,&#39; 00:00:00&#39;), concat(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 DAY),&#39;%Y-%m-%d&#39;),&#39; 23:59:59&#39; ) from dual;
</code></pre>
<h3 id="4-2-coalesce"><a href="#4-2-coalesce" class="headerlink" title="4-2.coalesce"></a>4-2.coalesce</h3><p>语法: <code>COALESCE ( expression1, expression2, ... expression-n );</code></p>
<p>表示如果第一个不为空取第一个，否则判断下一个，以此类推，如果全部为空，则返回null值。 </p>
<blockquote>
<p>对于Oracle数据库，一般经常对空值处理的函数为NVL，而mysql中常用到的是ifnull，这两个函数相似，其实都是由一个函数衍生而来，那就是COALESCE()函数。</p>
</blockquote>
<ul>
<li>MYSQL: <code>IFNULL(expression, value)</code></li>
<li>MSSQLServer: <code>ISNULL(expression, value)</code></li>
<li>Oracle: <code>NVL(expression,value)</code></li>
</ul>
<p><strong>在字段中使用</strong></p>
<pre><code class="sql">select ename , coalesce(comm,&#39;没福利&#39;) as comm from emp;</code></pre>
<p>等价于</p>
<pre><code class="sql">SELECT ename
     , CASE
           WHEN comm IS NOT NULL THEN comm
           ELSE
               &#39;没福利&#39; END as comm
FROM emp;</code></pre>
<p>结果:</p>
<table>
<thead>
<tr>
<th align="left">comm</th>
</tr>
</thead>
<tbody><tr>
<td align="left">没福利</td>
</tr>
<tr>
<td align="left">300.00</td>
</tr>
<tr>
<td align="left">500.00</td>
</tr>
</tbody></table>
<p><strong>在条件中使用</strong></p>
<p>在条件中最好不要使用<code>COALESCE</code>函数，使用<code>AND,OR,IS NULL和括号</code>方式来替换掉。</p>
<ul>
<li><a href="https://www.jb51.cc/mysql/433636.html" target="_blank" rel="noopener">MySQL的WHERE子句中的CASE或COALESCE性能</a><blockquote>
<p>下面sql只要看懂意思，不用太关注细节，主要明白一件事情：在where条件中尽量使用<code>AND,OR,IS NULL和括号</code>，不要使用<code>COALESCE</code>函数</p>
</blockquote>
</li>
</ul>
<p><strong>方式一</strong></p>
<pre><code class="sql">WHERE
    u.id = COALESCE(user_id, su.custom_id)</code></pre>
<p>修改方案</p>
<pre><code class="sql">WHERE
    (user_id IS NULL OR u.id = user_id) AND
    (su.custom_id = student_number) </code></pre>
<p><strong>方式二</strong></p>
<pre><code class="sql">WHERE
CASE 
    WHEN user_id IS NOT NULL AND LENGTH(user_id) &gt; 0
    THEN
        u.id = user_id 
    ELSE
        su.custom_id = student_number
    END</code></pre>
<p>修改方案</p>
<pre><code class="sql">WHERE
    (user_id IS NOT NULL AND LENGTH(user_id) &gt; 0 AND u.id = user_id)
    OR
    (su.custom_id = student_number)</code></pre>
<h2 id="5-链接"><a href="#5-链接" class="headerlink" title="5.链接"></a>5.链接</h2><ul>
<li><a href="https://dev.mysql.com/downloads/mysql/" target="_blank" rel="noopener">mysql下载地址</a></li>
<li><a href="https://blog.csdn.net/ychgyyn/article/details/84404217" target="_blank" rel="noopener">Windows下MySQL的安装</a></li>
<li><a href="https://blog.csdn.net/a2940093904/article/details/90382347" target="_blank" rel="noopener">Zip的下载和安装</a></li>
<li><a href="https://www.runoob.com/mysql/mysql-tutorial.html" target="_blank" rel="noopener">菜鸟教程</a></li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/" target="_blank" rel="noopener">mysql官网doc</a></li>
<li><a href="https://www.runoob.com/mysql/mysql-functions.html" target="_blank" rel="noopener">菜鸟教程-mysql</a></li>
<li><a href="https://blog.csdn.net/sinat_38899493/article/details/78710482" target="_blank" rel="noopener">MySql常用函数大全讲解</a></li>
</ul>

      
       <hr><span style="font-style: italic;color: gray;"> 转载请注明来源，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 157162006@qq.com </span>
    </div>
</article>


<p>
    <a  class="dashang" onclick="dashangToggle()">赏</a>
</p>


<div class="article_copyright">
    <p><span class="copy-title">文章标题:</span>mysql常用sql</p>
    <p><span class="copy-title">字数:</span><span class="post-count">4.4k</span></p>
    <p><span class="copy-title">本文作者:</span><a  title="沐雨云楼">沐雨云楼</a></p>
    <p><span class="copy-title">发布时间:</span>2020-08-19, 21:30:00</p>
    <p><span class="copy-title">最后更新:</span>2020-10-06, 10:20:38</p>
    <span class="copy-title">原始链接:</span><a class="post-url" href="/blog/2020/08/19/mysql-common-sql/" title="mysql常用sql">https://iworkh.gitee.io/blog/2020/08/19/mysql-common-sql/</a>
    <p>
        <span class="copy-title">版权声明:</span><i class="fa fa-creative-commons"></i> <a rel="license noopener" href="http://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank" title="CC BY-NC-SA 4.0 International" target = "_blank">&#34;署名-非商用-相同方式共享 4.0&#34;</a> 转载请保留原文链接及作者。
    </p>
</div>



    <div id="comments"></div>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">

<script type="text/javascript">
    $.getScript('/blog/js/gitalk.js', function () {
        var gitalk = new Gitalk({
            clientID: 'e4a269e1ae1600031361',
            clientSecret: '6f3f981cbc49dd802fde779697d8f01da3a77981',
            repo: 'iworkh.github.io',
            owner: 'iworkh',
            admin: ['iworkh'],
            id: decodeURI(location.pathname),
            distractionFreeMode: 'true',
            language: 'zh-CN',
            perPage: parseInt('10',10)
        })
        gitalk.render('comments')
    })
</script>




    




    </div>
    <div class="copyright">
        <p class="footer-entry">©2020 iworkh</p>
<!--<p class="footer-entry">Built with <a href="https://hexo.io/" target="_blank">Hexo</a> and <a href="https://github.com/yelog/hexo-theme-3-hexo" target="_blank">3-hexo</a> theme</p>-->

    </div>
    <div class="full-toc">
        <button class="full" data-title="切换全屏 快捷键 s"><span class="min "></span></button>
<button class="post-toc-menu" data-title="打开TOC 快捷键 w"><span class="post-toc-menu-icons"></span></button>
<div class="post-toc"><span class="post-toc-title">目录</span>
    <div class="post-toc-content">

    </div>
</div>
<a class="" id="rocket" ></a>

    </div>
</div>
<div class="acParent"></div>

<div class="hide_box" onclick="dashangToggle()"></div>
<div class="shang_box">
    <a class="shang_close"  onclick="dashangToggle()">×</a>
    <div class="shang_tit">
        <p>喜欢就点赞,疼爱就打赏</p>
    </div>
    <div class="shang_payimg">
        <div class="pay_img">
            <img src="/blog/img/alipay.jpg" class="alipay" title="扫码支持">
            <img src="/blog/img/weixin.jpg" class="weixin" title="扫码支持">
        </div>
    </div>
    <div class="shang_payselect">
        <span><label><input type="radio" name="pay" checked value="alipay">支付宝</label></span><span><label><input type="radio" name="pay" value="weixin">微信</label></span>
    </div>
</div>


</body>
<script src="/blog/js/jquery.pjax.js?v=1.0.1" ></script>

<script src="/blog/js/script.js?v=1.0.1" ></script>
<script>
    var img_resize = 'default';
    /*作者、标签的自动补全*/
    $(function () {
        $('.search').AutoComplete({
            'data': ['@沐雨云楼','@iworkh','#ai','#attr','#aysnc','#beanmap','#bigdecimal','#blog','#chrome','#dict','#dll','#docker','#domain','#fastjson','#func','#git','#github','#hadoop','#hexo','#idea','#iworkh','#jackson','#java','#jdni','#jfinalshell','#joplin','#JPA','#lock','#markdowm','#maven','#mock','#mq','#mycat','#mysql','#nginx','#notebook','#plan','#powermock','#python','#redis','#rest','#sharding-jdbc','#spark','#springboot','#test','#tool','#tools','#tornado','#transactional','#web','#windows','#wtforms','#zookeeper','#分库分表','#分页','#命令','#工具','#并发','#序列化','#微服务','#性能分析','#手册','#有道云','#标签','#模板','#百度云盘','#结构和算法','#计划','#迁移','#面试',],
            'itemHeight': 20,
            'width': 418
        }).AutoComplete('show');
    })
    function initArticle() {
        /*渲染对应的表格样式*/
        
            $(".post .pjax table").addClass("green_title");
        

        /*渲染打赏样式*/
        
        $("input[name=pay]").on("click", function () {
            if($("input[name=pay]:checked").val()=="weixin"){
                $(".shang_box .shang_payimg .pay_img").addClass("weixin_img");
            } else {
                $(".shang_box .shang_payimg .pay_img").removeClass("weixin_img");
            }
        })
        

        /*高亮代码块行号*/
        
        $('pre code').each(function(){
            var lines = $(this).text().trim().split('\n').length, widther='';
            if (lines>99) {
                widther = 'widther'
            }
            var $numbering = $('<ul/>').addClass('pre-numbering ' + widther).attr("unselectable","on");
            $(this).addClass('has-numbering ' + widther)
                    .parent()
                    .append($numbering);
            for(var i=1;i<=lines;i++){
                $numbering.append($('<li/>').text(i));
            }
        });
        

        /*访问数量*/
        
        $.getScript("//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js");
        

        /*代码高亮，行号对齐*/
        $('.pre-numbering').css('line-height',$('.has-numbering').css('line-height'));

        
        
    }

    /*打赏页面隐藏与展示*/
    
    function dashangToggle() {
        $(".shang_box").fadeToggle();
        $(".hide_box").fadeToggle();
    }
    

</script>

<!--加入行号的高亮代码块样式-->

<style>
    pre{
        position: relative;
        margin-bottom: 24px;
        border-radius: 10px;
        border: 1px solid #e2dede;
        background: #FFF;
        overflow: hidden;
    }
    code.has-numbering{
        margin-left: 30px;
    }
    code.has-numbering.widther{
        margin-left: 35px;
    }
    .pre-numbering{
        margin: 0px;
        position: absolute;
        top: 0;
        left: 0;
        width: 20px;
        padding: 0.5em 3px 0.7em 5px;
        border-right: 1px solid #C3CCD0;
        text-align: right;
        color: #AAA;
        background-color: #fafafa;
    }
    .pre-numbering.widther {
        width: 35px;
    }
</style>

<!--自定义样式设置-->
<style>
    
    
    .nav {
        width: 442px;
    }
    .nav.fullscreen {
        margin-left: -442px;
    }
    .nav-left {
        width: 120px;
    }
    
    
    @media screen and (max-width: 1468px) {
        .nav {
            width: 442px;
        }
        .nav.fullscreen {
            margin-left: -442px;
        }
        .nav-left {
            width: 150px;
        }
    }
    
    
    @media screen and (max-width: 1024px) {
        .nav {
            width: 442px;
            margin-left: -442px
        }
        .nav.fullscreen {
            margin-left: 0;
        }
        .nav .hide-list.fullscreen {
            left: 442px
        }
    }
    
    @media screen and (max-width: 426px) {
        .nav {
            width: 100%;
        }
        .nav-left {
            width: 100%;
        }
    }
    
    
    .nav-right .title-list nav a .post-title, .nav-right .title-list #local-search-result a .post-title {
        color: #383636;
    }
    
    
    .nav-right .title-list nav a .post-date, .nav-right .title-list #local-search-result a .post-date {
        color: #5e5e5f;
    }
    
    
    .nav-right nav a.hover, #local-search-result a.hover{
        background-color: #e2e0e0;
    }
    
    

    /*列表样式*/
    
    .post .pjax article .article-entry>ol, .post .pjax article .article-entry>ul, .post .pjax article>ol, .post .pjax article>ul{
        border: #e2dede solid 1px;
        border-radius: 10px;
        padding: 10px 32px 10px 56px;
    }
    .post .pjax article .article-entry li>ol, .post .pjax article .article-entry li>ul,.post .pjax article li>ol, .post .pjax article li>ul{
        padding-top: 5px;
        padding-bottom: 5px;
    }
    .post .pjax article .article-entry>ol>li, .post .pjax article .article-entry>ul>li,.post .pjax article>ol>li, .post .pjax article>ul>li{
        margin-bottom: auto;
        margin-left: auto;
    }
    .post .pjax article .article-entry li>ol>li, .post .pjax article .article-entry li>ul>li,.post .pjax article li>ol>li, .post .pjax article li>ul>li{
        margin-bottom: auto;
        margin-left: auto;
    }
    

    /* 背景图样式 */
    
    


    /*引用块样式*/
    

    /*文章列表背景图*/
    

    
</style>






<div class="mobile-menus-out" >

</div>
<div class="mobile-menus">
    
    
    
    <a class="dynamic-menu " target="_blank"  href="https://pgmanor.gitee.io/blog/">pgmanor</a>
    
    
    <a class="dynamic-menu " target="_blank"  href="https://www.iworkh.com/manualIt/Category/scopeDev/">iworkh</a>
    
    
    <a class="dynamic-menu " target="_blank"  href="https://gitee.com/iworkh">gitee</a>
    
</div>


<div style="position:absolute; bottom: 0; right: 0;">
    <iframe frameborder="no" border="0" marginwidth="0" marginheight="0" width=150 height=40 src="//music.163.com/outchain/player?type=2&id=1323150930&auto=0&height=32"></iframe>
</div>

</html>
